package com.yhp.dao.impl;

import com.yhp.bean.Student;
import com.yhp.dao.DBUtils;
import com.yhp.dao.StudentDao;
import com.yhp.util.StudentEnum;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class StudentDaoImple extends DBUtils implements StudentDao {
    @Override
    public int total(String name, String stuno, int sex) {
        ArrayList params = new ArrayList();
        int total = 0;
        try {
            StringBuffer sqlbuf=new StringBuffer(" select count(*) from student where 1=1 and state!=4 ");
            if (name!=null&&name.length()>0){
                sqlbuf.append(" and stuname like ? ");
                params.add("%"+name+"%");
            }
            if (stuno!=null&&stuno.length()>0){
                sqlbuf.append(" and stuno=? ");
                params.add(stuno);
            }
            if (sex!=-1){
                sqlbuf.append(" and sex=? ");
                params.add(sex);
            }
            ResultSet resultSet =query(sqlbuf.toString(),params);
            while (resultSet.next()){
                total = resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return total;
    }

    @Override
    public List<Student> getStudents(String name,String stuno,int sex,int pageIndex,int pageSize) {
        ArrayList arrayList = new ArrayList();
        ArrayList params = new ArrayList();
        try {
           StringBuffer sqlbuf=new StringBuffer(" select * from student where 1=1 and state!=4 ");
           if (name!=null&&name.length()>0){
               sqlbuf.append(" and stuname like ? ");
               params.add("%"+name+"%");
           }
           if (stuno!=null&&stuno.length()>0){
               sqlbuf.append(" and stuno=? ");
               params.add(stuno);
           }
           if (sex!=-1){
               sqlbuf.append(" and sex=? ");
               params.add(sex);
           }
           //分页
            sqlbuf.append(" limit ?,? "); //1 5 limit 0,5
            params.add((pageIndex-1)*pageSize);
            params.add(pageSize);

            ResultSet resultSet = query(sqlbuf.toString(),params);
            if(resultSet==null){ return null;}
            while (resultSet.next()){
            Student student = new Student();
                student.setStuid(resultSet.getInt("stuid"));
                student.setStuname(resultSet.getString("stuname"));
                student.setStuno(resultSet.getString("stuno"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getLong("phone"));
                student.setEmail(resultSet.getString("email"));
                student.setRegistered(resultSet.getString("registered"));
                student.setAddress(resultSet.getString("address"));
                student.setProfession(resultSet.getString("profession"));
                student.setIdnumber(resultSet.getLong("idnumber"));
                student.setPolitics(resultSet.getString("politics"));
                student.setRegdate(resultSet.getDate("regdate"));
                student.setState(resultSet.getInt("state"));
                student.setIntroduction(resultSet.getString("introduction"));
                student.setGid(resultSet.getInt("gid"));
                arrayList.add(student);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return arrayList;
    }

    @Override
    public int addStudent(Student student) {
        int a = 0;
        try {
            String sql = "insert into student values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            List params = new ArrayList();
            params.add(student.getStuname());
            params.add(student.getStuno());
            params.add(student.getSex());
            params.add(student.getPhone());
            params.add(student.getEmail());
            params.add(student.getRegistered());
            params.add(student.getAddress());
            params.add(student.getProfession());
            params.add(student.getIdnumber());
            params.add(student.getPolitics());
            params.add(new Date());
            params.add(StudentEnum.READING.type);//1表示再读 2表示休学 3表示毕业 4表示删除
            params.add(student.getIntroduction());
            params.add(student.getGid());
            a = update(sql,params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return a;
    }

    @Override
    public Student findStudent(int id) {
        Student student = null;
        try {
            String sql = "select * from student where stuid = ? ";
            List params= new ArrayList();
            params.add(id);
            ResultSet resultSet = query(sql, params);
            if (resultSet.next()){
                student = new Student();
                student.setStuid(resultSet.getInt("stuid"));
                student.setStuname(resultSet.getString("stuname"));
                student.setStuno(resultSet.getString("stuno"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getLong("phone"));
                student.setEmail(resultSet.getString("email"));
                student.setRegistered(resultSet.getString("registered"));
                student.setAddress(resultSet.getString("address"));
                student.setProfession(resultSet.getString("profession"));
                student.setIdnumber(resultSet.getLong("idnumber"));
                student.setPolitics(resultSet.getString("politics"));
                student.setRegdate(resultSet.getDate("regdate"));
                student.setState(resultSet.getInt("state"));
                student.setIntroduction(resultSet.getString("introduction"));
                student.setGid(resultSet.getInt("gid"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return student;
    }

    @Override
    public int upDate(Student student) {
        int a = 0;
        try {
            //完成非主键以外的信息更新
            String sql
                    = "update student set stuname=?,stuno=?,sex=?,phone=?,email=?,registered=?,Address=?,Profession=?,Idnumber=?,Politics=?,Introduction=?,Gid=? where stuid=? ";
            List params = new ArrayList();
            params.add(student.getStuname());
            params.add(student.getStuno());
            params.add(student.getSex());
            params.add(student.getPhone());
            params.add(student.getEmail());
            params.add(student.getRegistered());
            params.add(student.getAddress());
            params.add(student.getProfession());
            params.add(student.getIdnumber());
            params.add(student.getPolitics());
            params.add(student.getIntroduction());
            params.add(student.getGid());
            params.add(student.getStuid());
            a = update(sql,params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return a;
    }

    @Override
    public int deleteStudent(int id) {
        int a = 0;
        try {
            //完成非主键以外的信息更新
            String sql
                    = "update student set state=? where stuid=? ";
            List params = new ArrayList();
            params.add(StudentEnum.DELET.type);// 4 表示删除
           params.add(id);
            a = update(sql,params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return a;
    }
}
